let db = require('../db');


//获取所有的小区
exports.villageList = async function () {
  let sql=`
  SELECT vi.id,
  vi.name,
  vi.address,
  ad.userName AS createdBy,
  DATE_FORMAT(vi.createdAt,'%Y-%m-%d %H:%i') AS createdAt 
  FROM  
    (SELECT *FROM  village  WHERE isdelete ='n' )  AS vi 
    LEFT JOIN  admin AS ad ON ad.id =vi.createdBy
      
    
  `  
 let data  =await db.query(sql);
 return data[0]; 
}

//添加小区

exports.villageInsert = async function(name,address,createdBy){
  let sql=`
  INSERT  INTO village(name,address,createdBy)
  VALUES(:name,:address,:createdBy)
  `
  let data = await db.query(sql,{name,address,createdBy});
  return data[0];
}

//修改小区信息

exports.updateVillage = async function(villageId,name,address){
  let sql=`
  UPDATE  village SET
  name=:name,
  address=:address
  where id =:villageId 
  `
  let data =await db.query(sql,{villageId,name,address});
  return data[0];
}

//伪删除
exports.villageDel = async function(villageId){
  let sql=`
  UPDATE  village SET 
  isdelete ='y'
  where id =:villageId 

  `
  let data =await db.query(sql,{villageId});
  return data[0];
}
//删除该小区下的所有building

exports.delBuildingByVillageId  = async function(villageId){
  let sql=`
  UPDATE  building SET 
  isdelete ='y'
  where villageId =:villageId   
  `
  let data  = await db.query(sql,{villageId});
  return data[0];
}

//删除该小区下的所有unit

exports.delUnitByVillageId  = async function(villageId){
  let sql=`
  UPDATE  unit SET  isdelete ='y' where villageId =:villageId
  `
  let data  = await db.query(sql,{villageId});
  return data[0];
}
//删除该小区下的所有住户
exports.delUserByVillageId = async function(villageId){

  let sql=`
  UPDATE user SET isdelete ='y' where villageId =:villageId
  `
  let data = await db.query(sql,{villageId});
  return data[0];
}

//获取该小区的所有住户成员信息

exports.getUserIdByVillageId  = async function(villageId){
  let sql=`
  SELECT id FROM user  where villageId =:villageId AND isdelete ='n'
  `
  let  data  = await db.query(sql,{villageId});
  return data[0];
}

//删除该小区所有住户成员信息

exports.delFamilyByVillageId  =async function(userId){
  let sql=`
  UPDATE  family SET   isdelete ='y'  where userId =:userId 
  `
  let data =await db.query(sql,{userId});
  return data[0];
}

//删除该小区下的所有设备

exports.delEquipmentByVillageId  = async function(villageId){
  let sql=`
  UPDATE   equipment  SET  isdelete ='y' where  villageId =:villageId 
  `
  let data  = await db.query(sql,{villageId});
  return data[0];
}

//删除该小区下的所有物业管理员账号

exports.delProperty  = async function(villageId){
let  sql=`
UPDATE  admin  SET  isdelete ='y'  where  villageId =:villageId 
`
let data  = await db.query(sql,{villageId});
return data[0]; 


}